﻿using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using System.Data.Common;
using System.Data;

namespace DBFirst
{
    class Program
    {
        static void Main(string[] args)
        {
            //using (Context.MyContext ctx = new Context.MyContext())
            //{
            //Models.Student s1 = new Models.Student();
            //s1.StuNo = "000000000027";
            //s1.StuName = "李四五六";
            //s1.Gender = "男";
            //s1.Birthday = DateTime.Parse("2000-8-9");
            //s1.Password = "123456";

            ////ctx.Students.Add(s1);
            //ctx.Set<Models.Student>().Add(s1);
            //ctx.SaveChanges();
            //Console.WriteLine("Added");

            /*
             * 用户登录模拟
             */
            //1.提示用户输入账号信息
            //Console.Write("请输入学号：");
            //String stuNo = Console.ReadLine();
            //Console.Write("请输入密码：");
            //String password = Console.ReadLine();

            ////2.查询用户信息（按学号和密码查询）
            //var s1 = ctx.Set<Models.Student>().SingleOrDefault(s => s.StuNo == stuNo && s.Password == password);

            ////3.判断是否允许登陆
            //if (s1 != null)
            //{
            //    Console.WriteLine("登陆成功");
            //}
            //else
            //{
            //    Console.WriteLine("用户不存在或密码不对");
            //}

            ///*
            // * 状态管理机制
            // */
            //Models.Student s1 = new Models.Student();
            //s1.StuNo = "000000000027";
            //s1.StuName = "李四五六";
            //s1.Gender = "男";
            //s1.Birthday = DateTime.Parse("2000-8-9");
            //s1.Password = "123456";

            //Console.WriteLine(ctx.Entry(s1).State);
            ////ctx.Set<Models.Student>().Add(s1);//和直接修改对象状态是等效的
            //ctx.Entry(s1).State = Microsoft.EntityFrameworkCore.EntityState.Added;
            //Console.WriteLine(ctx.Entry(s1).State);

            //ctx.Set<Models.Student>().Update(s1);
            //Console.WriteLine(ctx.Entry(s1).State);

            /*
             * 模拟修改某学号学生的信息（已知学生学号）
             * 1.输入老密码，判断密码是否正确
             * 2.修改成新密码
             */
            //1.获取学生详细信息
            //Console.Write("请输入学生学号：");
            //String stuNo = Console.ReadLine();
            //Models.Student s2 = ctx.Students.FirstOrDefault(m => m.StuNo==stuNo);

            //if (s2 != null)
            //{
            //    //2.验证老密码是否正确
            //    Console.Write("请输入老密码：");
            //    String oldpwd = Console.ReadLine();//用户输入的
            //    if (oldpwd == s2.Password) //老密码验证通过，允许修改新密码
            //    {
            //        Console.Write("身份验证通过，请输入新密码：");
            //        String newpwd = Console.ReadLine();
            //        s2.Password = newpwd;

            //        //3.新密码更新到数据库
            //        ctx.Students.Update(s2);
            //        ctx.SaveChanges();
            //    }
            //    else
            //    {
            //        Console.WriteLine("原密码匹配失败，不允许修改密码");
            //    }

            //}
            //else
            //{
            //    Console.WriteLine("输入学号有误，无该学生信息。");
            //}
            //}
            //using (Context.MyContext ctx = new Context.MyContext())
            //{
            //    Models.Course s1 = new Models.Course();
            //    Console.WriteLine("请输入课程号");
            //    s1.CourseNo = Console.ReadLine();
            //    Console.WriteLine("请输入课程名");
            //    s1.CourseName = Console.ReadLine();
            //    Console.WriteLine("请输入学分");
            //    int c = int.Parse(Console.ReadLine());
            //    s1.Credit = c;
            //    ctx.Courses.Add(s1);
            //    ctx.SaveChanges();
            //    Console.WriteLine("添加成功");
            //}

            /*
             * 查询操作
             */
            using (Context.MyContext ctx = new Context.MyContext())
            {
                //1.1 查询姓张的所有同学（模糊查询） linq语法
                //var stus = ctx.Students.Where(s => s.StuName.StartsWith("张"));
                var stus = ctx.Set<Models.Student>().Where(s => s.StuName.StartsWith("张"));
                foreach (var s in stus)
                {
                    Console.WriteLine($"{s.StuNo},{s.StuName}");
                }

                //1.2 linq的类似sql语句查询方式
                //var stus = from s in ctx.Students where s.StuName.StartsWith("张") select s;
                //foreach (var s in stus)
                //{
                //    Console.WriteLine($"{s.StuNo},{s.StuName}");
                //}

                //2. IN查询
                //String[] strs = { "000000000001", "000000000003", "000000000015" };
                //var stus = ctx.Set<Models.Student>().Where(s => strs.Contains(s.StuNo));
                //foreach (var s in stus)
                //{
                //    Console.WriteLine($"{s.StuNo},{s.StuName}");
                //}

                //3. 排序 如果调用两次OrderBy，最后一次会覆盖前一次排序规则
                //var stus = ctx.Set<Models.Student>().
                //    OrderByDescending(s => s.StuNo).OrderBy(s => s.StuName).
                //    Where(s=>s.Gender=="女");
                //foreach (var s in stus)
                //{
                //    Console.WriteLine($"{s.StuNo},{s.StuName}");
                //}


                //4. 投影操作
                //var stus = ctx.Set<Models.Student>().
                //    Where(s => s.StuName.Contains("'")).
                //    Select(s => new { s.StuName, Sex = s.Gender, s.Birthday });
                //foreach (var s in stus)
                //{
                //    Console.WriteLine($"{s.StuName},{s.Sex},{s.Birthday}");
                //}

                //6. 分页(每页3条数据，查询第2页)
                //Console.Write("请输入查询页数：");
                //int pageSize = 3;
                //int pageIndex = int.Parse(Console.ReadLine());
                //var stus = ctx.Set<Models.Student>().Skip((pageIndex - 1) * pageSize).Take(pageSize);
                //foreach (var s in stus)
                //{
                //    Console.WriteLine($"{s.StuNo},{s.StuName}");
                //}

                //7. 直接执行sql语句
                //7.1 非查询操作：Insert Update Delete
                //ctx.Database.ExecuteSqlRaw("DELETE FROM Student WHERE StuNo='000000000001'");

                //String stuNo = "000000000001";
                //ctx.Database.ExecuteSqlRaw("DELETE FROM Student WHERE StuNo={0}", stuNo);//参数化方式执行，自动处理非法字符


                //String stuName = "张'";
                //ctx.Database.ExecuteSqlInterpolated($"DELETE FROM Student WHERE StuName={stuName}");//内插语法，自动处理非法字符
                //Console.WriteLine("删除成功");

                //7.2 查询操作
                //String gender = "男";
                //var stus = ctx.Students.FromSqlInterpolated($"SELECT * FROM Student WHERE Gender={gender}");
                //foreach (var s in stus)
                //{
                //    Console.WriteLine($"{s.StuNo},{s.StuName}");
                //}

                //8 ADO.NET在EFCore中的使用，引入System.Data.Common
                //DbConnection conn = ctx.Database.GetDbConnection();
                //DbProviderFactory f = DbProviderFactories.GetFactory(conn); //数据库操作相关的服务提供方

                ////构造命令
                //String sql = "SELECT * FROM Student";
                //DbCommand cmd = f.CreateCommand();
                //cmd.CommandText = sql;
                //cmd.Connection = conn;

                ////请搬运工
                //DbDataAdapter adapter = f.CreateDataAdapter();
                //adapter.SelectCommand = cmd;

                //DataSet ds = new DataSet();
                //adapter.Fill(ds, "Students");

            }
        }
    }
}
